import base64
import datetime
from blueprints.library import library
from flask import render_template, request, make_response
from blueprints.globalReference import db_helper
import json
from blueprints.globalReference import User,login_manager
from flask_login import login_user,login_required,current_user
import time


@library.route('/logon', methods=['GET'])
def logon():
    """
    登录
    :return:
    """
    try:

        sql = "select id,name,password,student_id from gmq_bs_student "
        student_datas = db_helper.getSelectSql(sql)
        student_data = [{'id': data[0], 'name': data[1], 'password': data[2], 'student_id': data[3]} for data in student_datas]
        resp = render_template(
                             'logon.html',student_datas=student_data,)
    except Exception as e:
        resp = make_response(render_template('warn.html', errormsg=('出错了，错误码：%s ' % e)))
        return resp
    return resp

@library.route('/confired', methods=['post'])
def confired():
    """
    登录校验
    :return:
    """
    try:
        student_id = request.form.get('student_id')
        password = request.form.get('password')

        sql = "select id,name from gmq_bs_student WHERE student_id ='" + str(student_id) + "' and password='" + str(
            password) + "'"
        student_datas = db_helper.getSelectSql(sql)
        if student_datas:
            rs = {'data': student_id}
            login_user(User(student_id,password))
        else:
            rs = {'data': 'f'}

        return json.dumps(rs)

    except Exception as e:
        resp = make_response(render_template('warn.html', errormsg=('出错了，错误码：%s ' % e)))
        return resp


@library.route('/index', methods=['GET'])
@login_required
def index():
    """
    主页
    :return:
    """
    try:
        student_id = current_user.id
        sql = "select name,id from gmq_bs_student where student_id ='" + student_id + "'"
        student_datas = db_helper.getSelectSql(sql)
        student_data = [{'name': data[0],'id':data[1]} for data in student_datas]
        s_id=student_data[0]['id']

        sql = "select id,title,head_pic,link,introduce from gmq_bs_index_news "
        news_list=[]
        news_datas = db_helper.getSelectSql(sql)
        news_data = [{'id': data[0], 'title': data[1], 'head_pic': data[2], 'link': data[3],'introduce':data[4]} for data in
                        news_datas]

        bo = 'f'
        sql = "select id,booking_time,booking_seat from gmq_bs_yyd where user_ref ='" + str(s_id) + "' and state = '已预约'"
        yyd_datas = db_helper.getSelectSql(sql)
        if yyd_datas:
            yyd_data = [{'id': data[0], 'booking_time': data[1],'seat':data[2]} for data in yyd_datas]
            now = datetime.datetime.today()
            late_time = yyd_data[0]['booking_time'] + datetime.timedelta(minutes=30)
            if late_time < now:
                bo = 't'
                sql = "update gmq_bs_yyd set state = '违规' where user_ref = '" + str(s_id) + "'and state = '已预约'"
                db_helper.updateSql(sql)
                sql = "update gmq_bs_student set jf = jf-10 where id = '" + str(s_id) + "'"
                db_helper.updateSql(sql)
                sql = "update gmq_bs_seat set seat_state = TRUE where id = '" + str(yyd_data[0]['seat']) + "'"
                db_helper.updateSql(sql)

        sql = "select yyd.id,yyd.booking_time,seat.zwh,room.name from gmq_bs_yyd as yyd LEFT JOIN gmq_bs_seat " \
              "as seat on yyd.booking_seat = seat.id LEFT JOIN gmq_bs_seat_room as room on seat.room = room.id " \
              "where user_ref = '" + str(student_data[0]['id']) + "' and yyd.state in ('已预约') "
        yyy_yyd_data = db_helper.getSelectSql(sql)
        yyy_yyd_num = len(yyy_yyd_data)
        if yyy_yyd_data:
            yyy_yyd_data = [{'id': data[0], 'booking_time': data[1],'zwh':data[2],'room':data[3]} for data in yyy_yyd_data]
            now = datetime.datetime.today()
            yyy_yyd_data[0]['booking_time'] += datetime.timedelta(minutes=30)


        sql = "select yyd.id,yyd.booking_time,seat.zwh,room.name from gmq_bs_yyd as yyd LEFT JOIN gmq_bs_seat " \
              "as seat on yyd.booking_seat = seat.id LEFT JOIN gmq_bs_seat_room as room on seat.room = room.id " \
              "where user_ref = '" + str(student_data[0]['id']) + "' and yyd.state in ('已签到') "
        yqd_yyd_datas = db_helper.getSelectSql(sql)
        yqd_yyd_num = len(yqd_yyd_datas)
        yqd_yyd_data = [{'id': data[0], 'booking_time': data[1],'zwh':data[2],'room':data[3]} for data in yqd_yyd_datas]

        for news_data in news_data:
            news_data['head_pic'] = 'http://%s/library/index/head_pic/%s' % (
                request.host, news_data['id'])
            news_list.append(news_data)

        resp = make_response(
            render_template('index.html',news_data=news_list,student_data=student_data,yy_num=yyy_yyd_num,
                            qd_num=yqd_yyd_num,yyy_data=yyy_yyd_data,yqd_data=yqd_yyd_data))
    except Exception as e:
        resp = make_response(
            render_template('warn.html', errormsg=('出错了，错误码：%s ' % e)))
        return resp
    return resp

@library.route('/qd_check', methods=['post'])
@login_required
def qd_check():
    """
    签到
    :return:
    """
    try:
        student_id = current_user.id
        sql = "select id from gmq_bs_student where student_id ='"+str(student_id)+"'"
        student_data = db_helper.getSelectSql(sql)
        s_id = student_data[0][0]

        now = datetime.datetime.today()

        sql = "update gmq_bs_yyd set state = '已签到',qd_time = '"+str(now)+"' where user_ref = '"+str(s_id)+"'and state = '已预约'"
        db_helper.updateSql(sql)
        rs = {'data':'success'}
        return json.dumps(rs)
    except Exception as e:
        resp = make_response(
            render_template('warn.html',errormsg=('出错了，错误码：%s ' % e)))
        return resp

@library.route('/qt_check', methods=['post'])
@login_required
def qt_check():
    """
    签退
    :return:
    """
    try:
        student_id = current_user.id
        sql = "select id from gmq_bs_student where student_id ='"+str(student_id)+"'"
        student_data = db_helper.getSelectSql(sql)
        s_id = student_data[0][0]

        now = datetime.datetime.today()
        # now = now.strftime('%Y-%m-%d %H:%M:%S')
        sql = "select qd_time,booking_seat from gmq_bs_yyd where state ='已签到'and user_ref = '"+str(s_id)+"'"
        qd_times = db_helper.getSelectSql(sql)
        qd_time = qd_times[0][0]
        booking_seat = qd_times[0][1]
        if qd_time:
            xc_time = now-qd_time

        sql = "update gmq_bs_yyd set state = '已退座',qt_time = '"+str(now)+"',xc_time = '"+str(xc_time)+"' where user_ref = '"+str(s_id)+"'and state = '已签到'"
        db_helper.updateSql(sql)

        sql = "update gmq_bs_seat set seat_state = TRUE where id = '" + str(booking_seat) + "'"
        db_helper.updateSql(sql)

        sql = "update gmq_bs_student set jf = jf+5 where id = '" + str(s_id) + "'"
        db_helper.updateSql(sql)

        sql = "INSERT INTO gmq_bs_jfb (jf,student_ref,lx) values (5,'"+str(s_id)+"',1)"
        db_helper.updateSql(sql)
        rs = {'data':'success'}
        return json.dumps(rs)
    except Exception as e:
        resp = make_response(
            render_template('warn.html',errormsg=('出错了，错误码：%s ' % e)))
        return resp

@library.route('/qx_check', methods=['post'])
@login_required
def qx_check():
    """
    取消预约
    :return:
    """
    try:
        student_id = current_user.id
        sql = "select id from gmq_bs_student where student_id ='"+str(student_id)+"'"
        student_data = db_helper.getSelectSql(sql)
        s_id = student_data[0][0]

        sql = "select booking_seat from gmq_bs_yyd where user_ref = '"+str(s_id)+"'and state = '已预约'"
        seat_data = db_helper.getSelectSql(sql)
        seat_id = seat_data[0][0]

        sql = "update gmq_bs_yyd set state = '已取消' where user_ref = '"+str(s_id)+"'and state = '已预约'"
        db_helper.updateSql(sql)

        sql = "update gmq_bs_seat set seat_state = TRUE where id = '" + str(seat_id) + "'"
        db_helper.updateSql(sql)
        rs = {'data':'success'}
        return json.dumps(rs)
    except Exception as e:
        resp = make_response(
            render_template('warn.html',errormsg=('出错了，错误码：%s ' % e)))
        return resp

@library.route('/index/head_pic/<pic_id>', methods=['GET'])
def index_pic(pic_id):
    """
    主页新闻图片
    :return:
    """
    try:
        sql = "select head_pic from gmq_bs_index_news where id ='" + str(pic_id) + "'"
        head_pic = db_helper.getSelectSql(sql)
        data = base64.b64decode(head_pic[0][0])
        response = make_response(data)
        response.headers['Content-Type'] = 'image/jpeg'
        response.headers['Last-Modified'] = datetime.datetime.now()
        return response
    except Exception:
        response = make_response('')
        response.headers['Content-Type'] = 'image/jpeg'
        response.headers['Last-Modified'] = datetime.datetime.now()
        return response

@library.route('/news', methods=['GET'])
def news():
    """
    新闻页
    :return:
    """
    try:
        tid = int(request.args.get('model_id'))
        sql = "select id,title,introduce from gmq_bs_index_news where id ='"+ str(tid)+"'"
        news_datas = db_helper.getSelectSql(sql)
        news_data = [{'id': data[0], 'title': data[1],'introduce':data[2]} for data in
                        news_datas]

        # sql = "select name from gmq_bs_student where student_id ='"+str(student_id)+"'"
        # student_datas = db_helper.getSelectSql(sql)
        # student_data = [{'name': data[0]} for data in student_datas]
        resp = make_response(
            render_template(
                'news_page.html',
                news_data=news_data))
    except Exception as e:
        resp = make_response(
            render_template('warn.html',errormsg=('出错了，错误码：%s ' % e)))
        return resp
    return resp

@library.route('/seat_res', methods=['GET'])
@login_required
def seat_res():
    """
    座位预约
    :return:
    """
    try:
        student_id=current_user.id
        sql = "select id,name from gmq_bs_student where student_id ='" + student_id + "'"
        student_datas = db_helper.getSelectSql(sql)
        s_id = student_datas[0][0]

        sql = "select name from gmq_bs_seat_library"
        library_datas = db_helper.getSelectSql(sql)
        library_data = [{'name': data[0]} for data in library_datas]

        room_list = []
        sql = "select seat_pic,name,id from gmq_bs_seat_room where isactivity = TRUE "
        room_datas = db_helper.getSelectSql(sql)
        room_data = [{'seat_pic': data[0], 'name': data[1], 'id': data[2]} for data in room_datas]
        for room_data in room_data:
            room_data['seat_pic'] = 'http://%s/library/seat_res/head_pic/%s' % (
                request.host, room_data['id'])
            room_list.append(room_data)

        sql = "select id from gmq_bs_yyd where user_ref = '"+str(s_id)+"' and state in ('已预约','已签到') "
        yyd_datas = db_helper.getSelectSql(sql)
        yyd_num = len(yyd_datas)

        resp = make_response(
            render_template('seat_res.html',room_all_data=room_list,
                            library_data=library_data,student_data=student_datas,yyd_num=yyd_num))
    except Exception as e:
        resp = make_response(
            render_template('warn.html', errormsg=('出错了，错误码：%s ' % e)))
        return resp
    return resp

@library.route('/seat_res/head_pic/<pic_id>', methods=['GET'])
def seat_pic(pic_id):
    """
    教室座位图
    :return:
    """
    try:
        sql = "select seat_pic from gmq_bs_seat_room where id ='"+ str(pic_id)+"'"
        seat_pic = db_helper.getSelectSql(sql)
        data = base64.b64decode(seat_pic[0][0])
        response = make_response(data)
        response.headers['Content-Type'] = 'image/jpeg'
        response.headers['Last-Modified'] = datetime.datetime.now()
        return response
    except Exception:
        response = make_response('')
        response.headers['Content-Type'] = 'image/jpeg'
        response.headers['Last-Modified'] = datetime.datetime.now()
        return response

@library.route('/check_room', methods=['post'])
@login_required
def check_room():
    """
    查看教室座位
    :return:
    """
    try:
        student_id = current_user.id
        sql = "select id,name from gmq_bs_student where student_id ='" + student_id + "'"
        student_datas = db_helper.getSelectSql(sql)
        s_id = student_datas[0][0]

        room_select = request.form.get('room_select')
        sql = "select seat_pic,id from gmq_bs_seat_room where id ='"+str(room_select)+"'"
        room_datas = db_helper.getSelectSql(sql)
        room_data = [{'seat_pic': data[0],'id':data[1],'state':data[1]} for data in room_datas]
        id = room_data[0]['id']
        seat_pic = 'http://%s/library/seat_res/head_pic/%s' % (
            request.host, id)

        sql = "select zwh,id,seat_state from gmq_bs_seat where room='"+str(room_select)+"' order by id"
        seat_datas = db_helper.getSelectSql(sql)
        seat_data = [{'zwh': data[0], 'id': data[1],'state':data[2]} for data in seat_datas]

        sql = "select id from gmq_bs_yyd where user_ref = '" + str(s_id) + "' and state in ('已预约','已签到') "
        yyd_datas = db_helper.getSelectSql(sql)
        yyd_num = len(yyd_datas)

        rs = {'seat_pic':seat_pic,'seat_datas':seat_data,'yyd_num':yyd_num}
        return json.dumps(rs)
    except Exception as e:
        resp = make_response(
            render_template('warn.html',errormsg=('出错了，错误码：%s ' % e)))
        return resp

@library.route('/booking_seat', methods=['post'])
@login_required
def booking_seat():
    """
    座位预约
    :return:
    """
    try:
        student_id = current_user.id
        sql = "select id from gmq_bs_student where student_id ='"+str(student_id)+"'"
        student_data = db_helper.getSelectSql(sql)
        s_id = student_data[0][0]

        id_select = request.form.get('id')
        now = datetime.datetime.today()
        now = now.strftime('%Y-%m-%d %H:%M:%S')
        sql = "INSERT INTO gmq_bs_yyd (user_ref,booking_seat,booking_time,state,isactivity) values ('"+str(s_id)+"','"+str(id_select)+"','"+str(now)+"','已预约',True)"
        db_helper.updateSql(sql)
        sql = "update gmq_bs_seat set seat_state = False where id = '"+str(id_select)+"'"
        db_helper.updateSql(sql)
        rs = {'data':'success'}
        return json.dumps(rs)
    except Exception as e:
        resp = make_response(
            render_template('warn.html',errormsg=('出错了，错误码：%s ' % e)))
        return resp

@library.route('/jd_imgupload', methods=['POST'])
def jd_imgupload():
    """
    监督占座上传照片
    :return:
    """
    try:
        file_pic =  request.files.getlist("file[]")
        file_seat = request.form.get('seat')
        uploaded_files = request.files.getlist("file_pic")
        x=file_pic[0].read()
        file_pic[0].stream._file
        psycopg2.Binary(file_pic[0])
        # pic = db_helper.Binary(file_pic)
        sql = "insert into gmq_bs_jdzz (img) values('"+file_pic[0]+"')"
        db_helper.updateSql(sql)
        sql = "select zwh,id from gmq_bs_seat "
        room_datas = db_helper.getSelectSql(sql)
        room_data = [{'seat_pic': data[0],'id':data[1]} for data in room_datas]
        rs = {'seat_pic':seat_pic}
        return json.dumps(rs)
    except Exception as e:
        resp = make_response(
            render_template('warn.html',errormsg=('出错了，错误码：%s ' % e)))
        return resp

@library.route('/wg_jd', methods=['POST'])
@login_required
def wg_jd():
    """
    监督违规占座
    :return:
    """
    try:
        id_select = request.form.get('id')
        now = datetime.datetime.today()
        now = now.strftime('%Y-%m-%d %H:%M:%S')
        student_id = current_user.id
        sql = "select id,name from gmq_bs_student where student_id ='" + student_id + "'"
        student_datas = db_helper.getSelectSql(sql)
        s_id = student_datas[0][0]
        sql = "insert into gmq_bs_jdzz (student_ref,seat,create_date) values('" + str(s_id) + "','"+str(id_select)+"','"+now+"')"
        db_helper.updateSql(sql)
        rs = {'data': 'success'}
        return json.dumps(rs)

    except Exception as e:
        resp = make_response(
            render_template('warn.html', errormsg=('出错了，错误码：%s ' % e)))
        return resp



@library.route('/jdzz', methods=['GET'])
@login_required
def jdzz():
    """
    监督占座页面
    :return:
    """
    try:
        student_id = current_user.id
        sql = "select id,name from gmq_bs_student where student_id ='" + student_id + "'"
        student_datas = db_helper.getSelectSql(sql)
        s_id = student_datas[0][0]

        sql = "select name from gmq_bs_seat_library"
        library_datas = db_helper.getSelectSql(sql)
        library_data = [{'name': data[0]} for data in library_datas]

        room_list = []
        sql = "select seat_pic,name,id from gmq_bs_seat_room"
        room_datas = db_helper.getSelectSql(sql)
        room_data = [{'seat_pic': data[0], 'name': data[1], 'id': data[2]} for data in room_datas]
        for room_data in room_data:
            room_data['seat_pic'] = 'http://%s/library/seat_res/head_pic/%s' % (
                request.host, room_data['id'])
            room_list.append(room_data)

        sql = "select id from gmq_bs_yyd where user_ref = '" + str(s_id) + "' and state in ('已预约','已签到') "
        yyd_datas = db_helper.getSelectSql(sql)
        yyd_num = len(yyd_datas)

        resp = make_response(
            render_template('jdzz.html', room_all_data=room_list,
                            library_data=library_data, student_data=student_datas, yyd_num=yyd_num))
    except Exception as e:
        resp = make_response(
            render_template('warn.html', errormsg=('出错了，错误码：%s ' % e)))
        return resp
    return resp

@library.route('/syjl', methods=['GET'])
@login_required
def syjl():
    """
    使用记录
    :return:
    """
    try:
        student_id = current_user.id
        sql = "select id from gmq_bs_student where student_id ='" + str(student_id) + "'"
        student_data = db_helper.getSelectSql(sql)
        s_id = student_data[0][0]

        sql = "select yyd.id,yyd.booking_time,seat.zwh,yyd.state,yyd.qt_time,room.name from gmq_bs_yyd as yyd " \
              "LEFT JOIN gmq_bs_seat as seat on yyd.booking_seat = seat.id left JOIN gmq_bs_seat_room as room " \
              "on seat.room = room.id where yyd.user_ref ='"+str(s_id)+"' and yyd.isactivity = true"
        syjl_datas = db_helper.getSelectSql(sql)
        syjl_data = [{'id': data[0], 'booking_time': data[1], 'zwh': data[2], 'state': data[3],'qt_time':data[4],'room':data[5]} for data in syjl_datas]
        resp = make_response(
            render_template(
                             'syjl.html',syjl_data=syjl_data,))
    except Exception as e:
        resp = make_response(
            render_template('warn.html',errormsg=('出错了，错误码：%s ' % e)))
        return resp
    return resp

@library.route('/jfrw', methods=['GET'])
@login_required
def jfrw():
    """
    积分任务
    :return:
    """
    try:
        sql = "select jf,name,remark from gmq_bs_jfb_lx "
        jf_datas = db_helper.getSelectSql(sql)
        jf_data = [{'jf': data[0], 'name': data[1], 'remark': data[2]} for data in jf_datas]
        resp = make_response(
            render_template(
                             'jfrw.html',jf_data=jf_data,))
    except Exception as e:
        resp = make_response(
            render_template('warn.html',errormsg=('出错了，错误码：%s ' % e)))
        return resp
    return resp

@library.route('/jfsc', methods=['GET'])
@login_required
def jfsc():
    """
    积分商城
    :return:
    """
    try:
        student_id = current_user.id
        sql = "select id,jf from gmq_bs_student where student_id ='" + str(student_id) + "'"
        student_data = db_helper.getSelectSql(sql)
        jf = student_data[0][1]

        resp = make_response(
            render_template(
                             'jfsc.html',jf=jf))
    except Exception as e:
        resp = make_response(
            render_template('warn.html',errormsg=('出错了，错误码：%s ' % e)))
        return resp
    return resp

@library.route('/wgjfdh', methods=['POST'])
@login_required
def wgjfdh():
    """
    违规积分兑换
    :return:
    """
    try:
        student_id = current_user.id
        sql = "select id,jf from gmq_bs_student where student_id ='" + str(student_id) + "'"
        student_data = db_helper.getSelectSql(sql)
        s_id = student_data[0][0]
        jf = student_data[0][1]
        if jf>=15:
            sql = "select id from gmq_bs_yyd where user_ref = '"+str(s_id)+"' and isactivity = true ORDER BY booking_time desc"
            yyd_data = db_helper.getSelectSql(sql)
            yyd_id = yyd_data[0][0]
            sql = "update gmq_bs_yyd set isactivity = False where id = '"+str(yyd_id)+"'"
            db_helper.updateSql(sql)
            sql = "update gmq_bs_student set jf = jf-15 where id = '" + str(s_id) + "'"
            db_helper.updateSql(sql)
            rs = {'data':'1'}
        else:
            rs = {'data':'0'}
        return json.dumps(rs)
    except Exception as e:
        resp = make_response(
            render_template('warn.html',errormsg=('出错了，错误码：%s ' % e)))
        return resp
    return resp



@library.route('/phb', methods=['GET'])
@login_required
def phb():
    """
    排行榜
    :return:
    """
    try:
        student_id = current_user.id
        sql = "select id from gmq_bs_student where student_id ='" + str(student_id) + "'"
        student_data = db_helper.getSelectSql(sql)
        s_id = student_data[0][0]

        sql = "select yyd.id,student.name,yyd.qd_time,yyd.qt_time,student.id,yyd.xc_time from gmq_bs_yyd as yyd LEFT JOIN gmq_bs_student " \
              "as student on yyd.user_ref = student.id where yyd.state='已退座' order by student.id"
        yyd_datas = db_helper.getSelectSql(sql)
        yyd_order_data = [{'id': data[0], 'name': data[1], 'qd_time': data[2], 'qt_time': data[3],'student_id':data[4],'xc_time':data[5],'dd':0} for data in yyd_datas]

        phb_list = []
        for index in range(len(yyd_order_data)):
            if yyd_order_data[index]["xc_time"]:
                qt_time = yyd_order_data[index]["qt_time"]
                qd_time = yyd_order_data[index]["qd_time"]
                xc_time = qt_time-qd_time
                yyd_order_data[index]["xc_time"] = xc_time
                if index > 0 and yyd_order_data[index]["student_id"] == yyd_order_data[index - 1]["student_id"]:
                    yyd_order_data[index]["xc_time"] += yyd_order_data[index - 1]["xc_time"]
                if index > 0 and yyd_order_data[index]["student_id"] != yyd_order_data[index - 1]["student_id"]:
                    phb_list.append(yyd_order_data[index-1])
                if index == len(yyd_order_data)-1:
                    phb_list.append(yyd_order_data[index])
        resp = make_response(
            render_template('phb.html',yyd_data=yyd_order_data,phb_list=phb_list))
    except Exception as e:
        resp = make_response(
            render_template('warn.html',errormsg=('出错了，错误码：%s ' % e)))
        return resp
    return resp

@library.route('/wgjl', methods=['GET'])
@login_required
def wgjl():
    """
    违规记录
    :return:
    """
    try:
        student_id = current_user.id
        sql = "select id from gmq_bs_student where student_id ='" + str(student_id) + "'"
        student_data = db_helper.getSelectSql(sql)
        s_id = student_data[0][0]

        sql = "select yyd.id,yyd.booking_time,seat.zwh,yyd.state,yyd.qt_time,room.name from gmq_bs_yyd as yyd " \
              "LEFT JOIN gmq_bs_seat as seat on yyd.booking_seat = seat.id left JOIN gmq_bs_seat_room as room " \
              "on seat.room = room.id where user_ref ='" + str(s_id) + "' and yyd.state = '违规' and yyd.isactivity = True"
        wgjl_datas = db_helper.getSelectSql(sql)
        wgjl_data = [
            {'id': data[0], 'booking_time': data[1], 'zwh': data[2], 'state': data[3], 'qt_time': data[4],
             'room': data[5]} for data in wgjl_datas]

        resp = make_response(
            render_template('wgjl.html',wgjl_data=wgjl_data,))
    except Exception as e:
        resp = make_response(
            render_template('warn.html',errormsg=('出错了，错误码：%s ' % e)))
        return resp
    return resp

